/**
 * Created by wry on 2015/4/13.
 */
var Users = mongoose.model('user');
var async = require('async');
var neighbourActivity = mongoose.model('activity_neighbour');
var mysqlUtil = require('../../common/utils/mysqlUtil');
var Question = mongoose.model('question');
var Community = mongoose.model('community');

Date.prototype.format = function (format) {
    var o = {
        "M+": this.getMonth() + 1, //month
        "d+": this.getDate(), //day
        "h+": this.getHours(), //hour
        "m+": this.getMinutes(), //minute
        "s+": this.getSeconds(), //second
        "q+": Math.floor((this.getMonth() + 3) / 3), //quarter
        "S": this.getMilliseconds() //millisecond
    }
    if (/(y+)/.test(format)) format = format.replace(RegExp.$1,
        (this.getFullYear() + "").substr(4 - RegExp.$1.length));
    for (var k in o) if (new RegExp("(" + k + ")").test(format))
        format = format.replace(RegExp.$1,
            RegExp.$1.length == 1 ? o[k] :
                ("00" + o[k]).substr(("" + o[k]).length));
    return format;
}
if (!String.prototype.format) {
    String.prototype.format = function () {
        var args = arguments;
        return this.replace(/{(\d+)}/g, function (match, number) {
            return typeof args[number] != 'undefined'
                ? args[number]
                : match
                ;
        });
    };
}
function commentToString(f) {
    return f.toString().
        replace(/^[^\/]+\/\*!?/, '').
        replace(/\*\/[^\/]+$/, '');
}
function sqlConditionString(conditionList) {
    var len = conditionList.length
    if (len == 0)
        return ''
    var condition = "where "

    for (var i = 0; i < len; i++) {
        condition += conditionList[i]
        if (i != len - 1)
            condition += ' and '
    }
    return condition
}


exports.getCommunityMainStatsService = function (community, date, callback) {
    var conditionList = []
    if (date != 'all')
        conditionList.push('as_of_date="' + date + '"')
    if (community != 'every')
        conditionList.push('commId="' + community + '"')

    var condition = sqlConditionString(conditionList)
    var sql = 'select *, convert(as_of_date, char) `date` from 66xiaoqu_rec.stats ' + condition
    mysqlUtil.queryBI(sql, function (err, result) {
        callback(err, result)
    })
};

exports.getCommunityDauStatsService = function (community, date, callback) {
    var conditionList = []
    if (date != 'all')
        conditionList.push('as_of_date="' + date + '"')
    if (community != 'every')
        conditionList.push('commId="' + community + '"')

    var condition = sqlConditionString(conditionList)
    var sql = 'select activeusersdaily, activeusersweekly, realactiveusersdaily, realactiveusersweekly, convert(as_of_date, char) `date` from 66xiaoqu_rec.stats ' + condition
    mysqlUtil.queryBI(sql, function (err, result) {
        callback(err, result)
    })
};

//exports.getRetentionByCommService = function (community, interval, callback) {
//    var sql = commentToString(function () {
//        /*
//         select convert(batchDate, char) batchDate, convert(retentionPeriod, char) retentionPeriod, activeUsers, totalUsers, retentionRate from
//         66xiaoqu_rec.test_batchRetention
//         where commId='{0}' and `interval`='{1}'
//         */
//    }).format(community, interval)
//
//    mysqlUtil.queryBI(sql, function (err, result) {
//        callback(err, result)
//    })
//}

exports.getIntervalRetentionByCommService = function (interval, community, startDate, endDate, callback) {

    var defaultGroupByComm = ', community'
    var defaultCommJoin = 'and community = comm'
    var commCondition = "and community ='" + community + "'"
    var startDateCondition = ''
    var endDateCondition = ''

    if (community == 'all'){
        defaultGroupByComm = ''
        defaultCommJoin = ''
        commCondition = ''
    }
    if (startDate != 'all')
        startDateCondition = "and d.cdate >='" + startDate + "'"
    if (endDate != 'all')
        endDateCondition = "and d.cdate <='" + endDate+ "'"

    var intervalcdate = ''
    var intervaladate = ''
    var periodDiff = ''
    var limit = ''
    if(interval == 'daily'){
        intervalcdate = 'date(dateCreated)'
        intervaladate = 'date(actiondate)'
        periodDiff = 'datediff(d.adate, d.cdate)'
        limit = 'having adate between cdate and adddate(cdate, interval 7 day)'
    }else if(interval == 'weekly'){
        intervalcdate = 'adddate(date(dateCreated), INTERVAL -WEEKDAY(dateCreated) day)'
        intervaladate = 'adddate(date(actiondate), INTERVAL -WEEKDAY(actiondate) day)'
        periodDiff = 'floor(datediff(d.adate, d.cdate) / 7)'
    }else if(interval == 'monthly'){
        intervalcdate = "concat(date_format(LAST_DAY(dateCreated),'%Y-%m-'),'01')"
        intervaladate = "concat(date_format(LAST_DAY(actiondate),'%Y-%m-'),'01')"
        periodDiff = 'timestampdiff(month, d.cdate, d.adate)'
    }

    var sql = commentToString(function () {
        /*
         select convert(d.cdate, char) batchDate
         {0},
         convert(d.adate, char) adate, {7} period, active, total, active/total periodRet
         from (
         select {5} cdate, {6} adate, community, count(distinct uid) active
         from 66xiaoqu_rec.dau, 66xiaoqu_rec.user u
         where uid = _id
         and ((mobilephone not like '400%' and mobilephone not like '500%' and mobilephone not like '600%' and mobilephone not like '700%') or mobilephone is null)
         group by cdate, adate
         {0}
         {8}
         ) d,
         (
         select {5} cdate, community comm, count(_id) total
         from 66xiaoqu_rec.user
         where ((mobilephone not like '400%' and mobilephone not like '500%' and mobilephone not like '600%' and mobilephone not like '700%') or mobilephone is null)
         group by cdate
         {0}
         ) c
         where d.cdate = c.cdate
         {1}
         {2}
         {3}
         {4}
         order by d.cdate, d.adate
         */
    }).format(defaultGroupByComm, defaultCommJoin, commCondition, startDateCondition, endDateCondition, intervalcdate, intervaladate, periodDiff, limit)
    //logger.info(sql)
    mysqlUtil.queryBI(sql, function (err, result) {
        callback(err, result)
    })
}
